﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Entity;

namespace DataAccess
{
    public class GreenLifesDA
    {
        SqlDataAdapter da;

        public DataSet getGreenLifes()
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    DataSet dt = null;
                    da = new SqlDataAdapter("selectGreenLifes", connection);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    dt = new DataSet();
                    da.Fill(dt);
                    connection.Close();
                    return dt;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return null;
                }
            }
        }

        public bool CreateGreenLifes(GreenLifes gl)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand("addGreenLifes", connection);
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add("@GreenLifesTitle", SqlDbType.NVarChar);
                    command.Parameters["@GreenLifesTitle"].Value = gl.GreenLifesTitle.ToString();

                    command.Parameters.Add("@GreenLifesQuickTitle", SqlDbType.NVarChar);
                    command.Parameters["@GreenLifesQuickTitle"].Value = gl.GreenLifesQuickTitle.ToString();

                    command.Parameters.Add("@GreenLifesImages", SqlDbType.VarChar);
                    command.Parameters["@GreenLifesImages"].Value = gl.GreenLifesImages.ToString();

                    command.Parameters.Add("@GreenLifesContents", SqlDbType.NVarChar);
                    command.Parameters["@GreenLifesContents"].Value = gl.GreenLifesContents.ToString();

                    command.Parameters.Add("@GreenLifesStatus", SqlDbType.Int);
                    command.Parameters["@GreenLifesStatus"].Value = gl.GreenLifesStatus.ToString();


                    command.ExecuteNonQuery();
                    connection.Close();
                    return true;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return false;
                }
            }
        }

        public GreenLifes getGreenLifesById(int id)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand cmd = new SqlCommand("selectGreenLifesById", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@GreenLifesID", id));
                    SqlDataReader dr = cmd.ExecuteReader();
                    GreenLifes gl = null;
                    if (dr.Read())
                    {
                        int glId = Int32.Parse(dr["GreenLifesID"].ToString());
                        String glTitle = dr["GreenLifesTitle"].ToString();
                        String glQuickTitle = dr["GreenLifesQuickTitle"].ToString();
                        String glImage = dr["GreenLifesImages"].ToString();
                        String glContent = dr["GreenLifesContents"].ToString();
                        int glStatus = Int32.Parse(dr["GreenLifesStatus"].ToString());
                        gl = new GreenLifes(glId, glTitle, glQuickTitle, glImage, glContent, glStatus);
                    }
                    connection.Close();
                    return gl;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return null;
                }
            }
        }

        public bool UpdateGreenLifes(GreenLifes gl)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand("updateGreenLifes", connection);
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add("@GreenLifesID", SqlDbType.Int);
                    command.Parameters["@GreenLifesID"].Value = gl.GreenLifesID.ToString();

                    command.Parameters.Add("@GreenLifesTitle", SqlDbType.NVarChar);
                    command.Parameters["@GreenLifesTitle"].Value = gl.GreenLifesTitle.ToString();

                    command.Parameters.Add("@GreenLifesQuickTitle", SqlDbType.NVarChar);
                    command.Parameters["@GreenLifesQuickTitle"].Value = gl.GreenLifesQuickTitle.ToString();

                    command.Parameters.Add("@GreenLifesImages", SqlDbType.VarChar);
                    command.Parameters["@GreenLifesImages"].Value = gl.GreenLifesImages.ToString();

                    command.Parameters.Add("@GreenLifesContents", SqlDbType.NVarChar);
                    command.Parameters["@GreenLifesContents"].Value = gl.GreenLifesContents.ToString();

                    command.Parameters.Add("@GreenLifesStatus", SqlDbType.Int);
                    command.Parameters["@GreenLifesStatus"].Value = gl.GreenLifesStatus.ToString();

                    command.ExecuteNonQuery();
                    connection.Close();
                    return true;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return false;
                }
            }
        }

        public bool DeleteGreenLifes(GreenLifes gl)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand("removeGreenLifes", connection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter("@GreenLifesID", gl.GreenLifesID));
                    command.ExecuteNonQuery();
                    connection.Close();
                    return true;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return false;
                }
            }
        }

        //Lay 1 greenlife theo status
        public DataSet selectTopGreenLifeByStatus(int status)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    DataSet dt = null;
                    da = new SqlDataAdapter("selectTopGreenLifesByStatus", connection);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.SelectCommand.Parameters.Add(new SqlParameter("@GreenLifesStatus", status));
                    dt = new DataSet();
                    da.Fill(dt);
                    connection.Close();
                    return dt;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return null;
                }
            }
        }
        //Lay greenlife theo status []
        public DataSet selectGreenLifeByStatus(int status)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    DataSet dt = null;
                    da = new SqlDataAdapter("selectGreenLifesByStatus", connection);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.SelectCommand.Parameters.Add(new SqlParameter("@GreenLifesStatus", status));
                    dt = new DataSet();
                    da.Fill(dt);
                    connection.Close();
                    return dt;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return null;
                }
            }
        }

        //phan trang tien ich [spGreenLifeAll_PhanTrang]
        public DataSet PhanTrangGreenLifeByStatus(int currPage, int recodperpage, int Pagesize, int Status)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    DataSet dt = null;
                    da = new SqlDataAdapter("spGreenLife_PhanTrang", connection);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.SelectCommand.Parameters.Add(new SqlParameter("@currPage", currPage));
                    da.SelectCommand.Parameters.Add(new SqlParameter("@recodperpage", recodperpage));
                    da.SelectCommand.Parameters.Add(new SqlParameter("@Pagesize", Pagesize));
                    da.SelectCommand.Parameters.Add(new SqlParameter("@Status", Status));
                    dt = new DataSet();
                    da.Fill(dt);
                    connection.Close();
                    return dt;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return null;
                }
            }
        }

        //phan trang all
        public DataSet PhanTrangGreenLifeAllByStatus(int currPage, int recodperpage, int Pagesize)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    DataSet dt = null;
                    da = new SqlDataAdapter("spGreenLifeAll_PhanTrang", connection);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.SelectCommand.Parameters.Add(new SqlParameter("@currPage", currPage));
                    da.SelectCommand.Parameters.Add(new SqlParameter("@recodperpage", recodperpage));
                    da.SelectCommand.Parameters.Add(new SqlParameter("@Pagesize", Pagesize));
                    dt = new DataSet();
                    da.Fill(dt);
                    connection.Close();
                    return dt;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return null;
                }
            }
        }
    }
}
